package com.rivues.util.tools;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.h2.tools.Server;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.rivues.module.platform.web.model.Database;
import com.rivues.util.RivuTools;


public class H2Helper {
	private final static Logger log = LoggerFactory.getLogger(H2Helper.class); 
	private static Server server ;
	private static Connection conn ;
	private static String jdbcurl = null ;
	public static void startServer() throws SQLException{
		server = Server.createTcpServer(new String[] { "-tcpPort", "8000" }) .start();
	}
	/**
	 * 
	 * @return
	 * @throws SQLException
	 * @throws  
	 */
	public static Connection getConnection(Database db) throws Exception{
		if( db == null){
			return conn!=null && !conn.isClosed() ? conn : (conn = DriverManager.
			       getConnection("jdbc:h2:rivues", "sa", ""))  ;
		}else{
			try {
				Class.forName(db.getDriverclazz()) ;
				jdbcurl = db.getDatabaseurl();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
			return conn = conn!=null && !conn.isClosed() ? conn : (conn = DriverManager.
				       getConnection(db.getDatabaseurl(), db.getAccount(), RivuTools.decryption(db.getPassword())))  ;
		}
	}
	
	public static void executeSQL(String sql , Database db){
		executeSQL(sql , db , true) ;
	}
	
	/**
	 * 获取某个表的ddl
	 * @param tableName
	 * @param db
	 * @return
	 * @throws SQLException
	 */
	public static String getDDL(String tableName,Database db ) throws Exception{
		String str = "";
		if("mysql".equalsIgnoreCase(db.getSqldialect())){
			//如果是mysql
			str = "show create table "+tableName;
		} else if("oracle".equalsIgnoreCase(db.getSqldialect())){
			str = "select 1,DBMS_METADATA.GET_DDL('TABLE','"+tableName+"') from dual";
		}
		
		Connection conn = null ;
		Statement stat = null;
		ResultSet rs = null ;
		try {
			if(jdbcurl!=null && !jdbcurl.equals(db.getDatabaseurl()) && conn!=null && !conn.isClosed()){
				conn.close() ;
				conn = null ;
			}
			conn = getConnection(db) ;
			stat = conn.createStatement() ;
			log.info(str);
			rs = stat.executeQuery(str);
			str = "";
			while(rs.next()){
				str += rs.getString(2);
			}
			return str;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(rs!=null){
				rs.close();
			}
			if(stat!=null){
				try {
					stat.close() ;
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null){
				conn.close();
			}
		}
		return "";
	}
	

 
 
	
	@SuppressWarnings("unused")
	public static void executeSQL(String sql , Database db , boolean throwException){
		Connection conn = null ;
		Statement stat = null;
		try {
			if(jdbcurl!=null && !jdbcurl.equals(db.getDatabaseurl()) && conn!=null && !conn.isClosed()){
				conn.close() ;
				conn = null ;
			}
			conn = getConnection(db) ;
			stat = conn.createStatement() ;
			log.info(sql);
			stat.execute(sql) ;
			conn.commit();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			if(throwException){
				e.printStackTrace();
			}
		}finally{
			if(stat!=null){
				try {
					stat.close() ;
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close() ;
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
	}
	
	
	@SuppressWarnings("unused")
	public static void executeSQLOpenException(String sql , Database db)throws Exception{
		Connection conn = null ;
		Statement stat = null;
		try {
			if(jdbcurl!=null && !jdbcurl.equals(db.getDatabaseurl()) && conn!=null && !conn.isClosed()){
				conn.close() ;
				conn = null ;
			}
			conn = getConnection(db) ;
			stat = conn.createStatement() ;
			log.info(sql);
			stat.execute(sql) ;
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally{
			if(stat!=null){
				try {
					stat.close() ;
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close() ;
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
	}
	
	/**
	 * @param args
	 * @throws Exception 
	 */
	public static void init() throws Exception {
//		Class.forName("org.h2.Driver");
//		Connection conn = DriverManager.
//		       getConnection("jdbc:h2:mem:rivues;MODE=MySQL;IGNORECASE=true", "sa", "");
//		   // add application code here
//		   Statement stmt = conn.createStatement();
////		   stmt.executeUpdate("CREATE TABLE crm_asterisk_skill_record (  id varchar(60) PRIMARY KEY NOT NULL,  duration int(11) DEFAULT NULL,  tenant_id varchar(60) DEFAULT NULL,  call_type varchar(20) DEFAULT NULL,  ucid varchar(60) DEFAULT NULL,  skill varchar(60) DEFAULT NULL,  caller varchar(20) DEFAULT NULL,  in_queue_time datetime DEFAULT NULL,  out_queue_time datetime DEFAULT NULL,  queue_result varchar(200) DEFAULT NULL,  access_number varchar(60) DEFAULT NULL,  button_no varchar(60) DEFAULT NULL);");
////		   stmt.executeUpdate("CREATE TABLE crm_asterisk_skill_snap (  id varchar(60) PRIMARY KEY NOT NULL,  create_time datetime DEFAULT NULL,  tenant_id varchar(60) DEFAULT NULL,  skill varchar(60) DEFAULT NULL,  waiting_calls int(11) DEFAULT NULL,  agent_active_count int(11) DEFAULT NULL,  agent_call_count int(11) DEFAULT NULL,  discard_phone_count int(11) DEFAULT NULL,  agent_available_count int(11) DEFAULT NULL,  paused_members int(11) DEFAULT NULL);");
////		   
//		   FileInputStream input = new FileInputStream(RivuDataContext.REAL_PATH+File.separator+"data"+File.separator+"test.sql") ;
//		   InputStreamReader isr = new InputStreamReader(input,"UTF-8");
//		   BufferedReader br = new BufferedReader(isr);
//		   String data = null ;
//		   long start = System.currentTimeMillis() ;
//		   int i=0 ;
//		   StringBuffer strb = new StringBuffer();
//		   while((data = br.readLine())!=null){
//			   strb.append("\r\n").append(data);
//			   if(data.trim().endsWith(";")){
//				   data = strb.toString().replaceAll("ENGINE=[\\S\\s]*? CHARSET=[\\S\\s]*;", ";") ;
//				   strb = new StringBuffer();
//			   }else{
//				   continue;
//			   }
//			   if(data!=null && data.length()>0){
//				   stmt.executeUpdate(data);
//				   i++ ;
//				   if(i%100000==0){
//					   System.out.println(System.currentTimeMillis()-start +" 数据量："+i + " 速度："+i*1000/(System.currentTimeMillis()-start));
//				   }
////				   if(i>100000){
////					   break;
////				   }
//			   }
//		   }
//		   ResultSet rs = stmt.executeQuery("SELECT * FROM crm_asterisk_skill_snap");    
//		    while(rs.next()) {   
////		     System.out.println(rs.getString("id")+","+rs.getObject("create_time"));
//		    }
//		   conn.close();
	}
	public static void stopServer(){
		try {
			getConnection(null).close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(server!=null){
			server.shutdown();
		}
	}
	public static void loadDataFromDatabaseForTest(Database database) throws Exception{
//		List<RivuTableMetaData> rivuTableMetaList = DatabaseMetaDataHandler.getTables(database) ;
//		Connection conn = DriverManager.
//			       getConnection("jdbc:rivues:tcp://localhost:8000/mem:"+database.getOrgi()+";CACHE_SIZE=100000000", "sa", "");
//			   // add application code here
//		Statement stmt = conn.createStatement();
//		for(RivuTableMetaData metadata : rivuTableMetaList){
//			stmt.executeUpdate( DatabaseMetaDataHandler.getCreateSQL(metadata) );
//		}
//		conn.close();
	}
}
